
--
-- Generate persons table
--
create table persons(nif varchar(9) not null);

create or replace function generate_persons() returns void as $$
    begin
        for i in 1..11000 loop
            insert into persons(nif) select generate_NIF();
        end loop;
    end;
$$ language plpgsql;

select generate_persons();


--
--  Generate test data
--

create or replace function generate_test_data() returns void as $$
    declare
        the_date timestamp;
        the_import_date timestamp;
        the_action integer;
        council record;
        persons integer;
        person record;
        internet_connection int4;
        the_header int8;
        num_questions integer;
        question record;
        the_training_preference int4;
    begin
        for i in 1..200 loop
            -- Generate the day
            the_date := current_timestamp - ((random()*720)::integer * (interval '1 day'));
            the_import_date := the_date + ((random()*10)::integer * (interval '1 day'));
            -- Generate the action
            select id_action into the_action from training_actions order by hashfloat8(random()) limit 1;
            -- Iterate for councils
            for council in (select id_council from councils where id_province in ('15', '27', '32', '36')  order by hashfloat8(random()) limit 5) loop
                persons := 5 + (random()*20)::integer;              
                -- Iterate for persons
                for person in execute ('select nif from persons order by hashfloat8(random()) limit ' || persons) loop
                    internet_connection := (random()*2)::integer % 2;
                    the_header := nextval('survey_answer_headers_seq');
                    insert into survey_answer_headers(id_header, entity_id, internet, creation_date, import_date, id_council, id_survey, id_action, id_origin) values (the_header, person.nif, internet_connection, the_date, the_import_date, council.id_council, 1, the_action, 1);
                    -- Iterate for questions
                    num_questions := 3 + (random()*15)::integer;                   
                    for question in execute ('select id_question from questions where id_survey=1 order by hashfloat8(random()) limit ' || num_questions) loop
                        the_training_preference := (random()*3)::int4;
                        insert into survey_unit_answers(id_header, id_question, training_preference, covered) values (the_header, question.id_question, the_training_preference, false);
                    end loop;
                end loop;
            end loop;
        end loop;
    end;
$$ language plpgsql;


-- Perform generation
select generate_test_data();

-- Clean up
drop table persons;
